package com.cn.office;

import java.io.FileInputStream;
import java.io.FileOutputStream;
import java.io.IOException;
import java.io.InputStream;
import java.util.Iterator;

import org.apache.log4j.Logger;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.Row;

import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.poifs.filesystem.POIFSFileSystem;

/**
 * 微软excel文件操作模块
 * 
 */
public class ExcelUtil {

	/**
	 * 打印日志
	 */
	public Logger logger = Logger.getLogger(ExcelUtil.class);  

	/**
	 * 创建excel文件
	 * 
	 * @param filename
	 * @param workbook
	 * @throws IOException
	 */
	public void createExcelFile(String filename, HSSFWorkbook workbook)
			throws IOException {
		FileOutputStream fileOutputStream = new FileOutputStream(filename);
		workbook.write(fileOutputStream);
		fileOutputStream.close();
	}

	/**
	 * 读取excel表格文件内容
	 * 
	 * @param filepathname
	 * @throws IOException
	 */
	public void readExcelFile(String filepathname) throws IOException {
		InputStream stream = new FileInputStream(filepathname);
		POIFSFileSystem fs = new POIFSFileSystem(stream);
		/**
		 * 获取工作簿
		 */
		HSSFWorkbook workbook = new HSSFWorkbook(fs);
		int sheet_num = workbook.getNumberOfSheets();
		for (int i = 0; i < sheet_num; ++i) {
			/**
			 * 获取工作表
			 */
			HSSFSheet sheet = workbook.getSheetAt(i);

			Iterator<Row> rows = sheet.rowIterator();
			/**
			 * 获取行
			 */
			while (rows.hasNext()) {
				HSSFRow row = (HSSFRow) rows.next();
				Iterator<Cell> cells = row.cellIterator();
				/**
				 * 获取单元格
				 */
				while (cells.hasNext()) {
					HSSFCell cell = (HSSFCell) cells.next();
					switch (cell.getCellType()) {
					case Cell.CELL_TYPE_BLANK:
						logger.debug("");
						break;

					case Cell.CELL_TYPE_BOOLEAN:
						logger.debug(cell.getBooleanCellValue());
						break;
					case Cell.CELL_TYPE_ERROR:
						logger.debug(cell.getErrorCellValue());
						break;
					case Cell.CELL_TYPE_FORMULA:
						logger.debug(cell.getCellFormula());
						break;
					case Cell.CELL_TYPE_NUMERIC:
						logger.debug(cell.getNumericCellValue());
						break;
					case Cell.CELL_TYPE_STRING:
						logger.debug(cell.getStringCellValue());
						break;
					default:
						break;
					}
				}
			}
		}

	}

	// 应用实例：创建一个excel计算表
	public static void main(String[] args) throws Exception {
//		// 创建工作簿
//		HSSFWorkbook workbook = new HSSFWorkbook();
//		// 创建工作表
//		HSSFSheet sheet = workbook.createSheet();
//		for (int i = 0; i < 100; ++i) {
//			// 创建单元格
//			HSSFRow row = sheet.createRow(i);
//			HSSFCell cell = row.createCell(0);
//			// 设置数值型单元格
//			cell.setCellType(Cell.CELL_TYPE_NUMERIC);
//			// 设置单元格的数值
//			Random random = new Random();
//			cell.setCellValue(random.nextInt(1000));
//		}
//		// 创建计算单元格
//		HSSFRow row = sheet.createRow(100);
//		HSSFCell cell = row.createCell(0);
//		// 设置单元格为公式类型
//		cell.setCellType(Cell.CELL_TYPE_FORMULA);
//		// 输入单元格中的计算公式，注意不能以等号开始
//		cell.setCellFormula("AVERAGE(A1:A100)");
//		// 写入excel文件
		ExcelUtil excel = new ExcelUtil();
//		excel.createExcelFile("d:/abcd.xls", workbook);

		excel.readExcelFile("d:\\efgh.xls");
	}

}
